{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise 03 - Columnar Vs Row Storage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The columnar storage extension used here: \n",
    "    - cstore_fdw by citus_data [https://github.com/citusdata/cstore_fdw](https://github.com/citusdata/cstore_fdw)\n",
    "- The data tables are the ones used by citus_data to show the storage extension\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 0 : Connect to the local database where Pagila is loaded\n",
    "\n",
    "### Create the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE DATABASE\n",
      "--2019-06-03 17:44:57--  http://examples.citusdata.com/customer_reviews_1998.csv.gz\n",
      "Resolving examples.citusdata.com (examples.citusdata.com)... 104.25.47.11, 104.25.46.11, 2606:4700:20::6819:2f0b, ...\n",
      "Connecting to examples.citusdata.com (examples.citusdata.com)|104.25.47.11|:80... connected.\n",
      "HTTP request sent, awaiting response... 301 Moved Permanently\n",
      "Location: https://examples.citusdata.com/customer_reviews_1998.csv.gz [following]\n",
      "--2019-06-03 17:44:57--  https://examples.citusdata.com/customer_reviews_1998.csv.gz\n",
      "Connecting to examples.citusdata.com (examples.citusdata.com)|104.25.47.11|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 24774482 (24M) [application/x-gzip]\n",
      "Saving to: ‘customer_reviews_1998.csv.gz’\n",
      "\n",
      "customer_reviews_19 100%[===================>]  23.63M  50.1MB/s    in 0.5s    \n",
      "\n",
      "2019-06-03 17:44:58 (50.1 MB/s) - ‘customer_reviews_1998.csv.gz’ saved [24774482/24774482]\n",
      "\n",
      "URL transformed to HTTPS due to an HSTS policy\n",
      "--2019-06-03 17:44:58--  https://examples.citusdata.com/customer_reviews_1999.csv.gz\n",
      "Resolving examples.citusdata.com (examples.citusdata.com)... 104.25.47.11, 104.25.46.11, 2606:4700:20::6819:2f0b, ...\n",
      "Connecting to examples.citusdata.com (examples.citusdata.com)|104.25.47.11|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 48996256 (47M) [application/x-gzip]\n",
      "Saving to: ‘customer_reviews_1999.csv.gz’\n",
      "\n",
      "customer_reviews_19 100%[===================>]  46.73M  56.6MB/s    in 0.8s    \n",
      "\n",
      "2019-06-03 17:44:59 (56.6 MB/s) - ‘customer_reviews_1999.csv.gz’ saved [48996256/48996256]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!sudo -u postgres psql -c 'CREATE DATABASE reviews;'\n",
    "\n",
    "!wget http://examples.citusdata.com/customer_reviews_1998.csv.gz\n",
    "!wget http://examples.citusdata.com/customer_reviews_1999.csv.gz\n",
    "\n",
    "!gzip -d customer_reviews_1998.csv.gz \n",
    "!gzip -d customer_reviews_1999.csv.gz \n",
    "\n",
    "!mv customer_reviews_1998.csv /tmp/customer_reviews_1998.csv\n",
    "!mv customer_reviews_1999.csv /tmp/customer_reviews_1999.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-11T17:33:34.685025Z",
     "start_time": "2019-02-11T17:33:34.514916Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://student:student@127.0.0.1:5432/reviews\n"
     ]
    }
   ],
   "source": [
    "DB_ENDPOINT = \"127.0.0.1\"\n",
    "DB = 'reviews'\n",
    "DB_USER = 'student'\n",
    "DB_PASSWORD = 'student'\n",
    "DB_PORT = '5432'\n",
    "\n",
    "# postgresql://username:password@host:port/database\n",
    "conn_string = \"postgresql://{}:{}@{}:{}/{}\" \\\n",
    "                        .format(DB_USER, DB_PASSWORD, DB_ENDPOINT, DB_PORT, DB)\n",
    "\n",
    "print(conn_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-02-11T17:33:40.572334Z",
     "start_time": "2019-02-11T17:33:39.203283Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@reviews'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 1 :  Create a table with a normal  (Row) storage & load data\n",
    "\n",
    "**TODO:** Create a table called customer_reviews_row with the column names contained in the `customer_reviews_1998.csv` and `customer_reviews_1999.csv` files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS customer_reviews_row;\n",
    "CREATE TABLE customer_reviews_row\n",
    "(\n",
    "    customer_id TEXT,\n",
    "    review_date DATE,\n",
    "    review_rating INTEGER,\n",
    "    review_votes INTEGER,\n",
    "    review_helpful_votes INTEGER,\n",
    "    product_id CHAR(10),\n",
    "    product_title TEXT,\n",
    "    sales_rank BIGINT,\n",
    "    product_group TEXT,\n",
    "    product_category TEXT,\n",
    "    product_subcategory TEXT,\n",
    "    similar_product_ids CHAR(10)[]\n",
    "    \n",
    "    \n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TODO:** Use the [COPY statement](https://www.postgresql.org/docs/9.2/sql-copy.html) to populate the tables with the data in the `customer_reviews_1998.csv` and `customer_reviews_1999.csv` files. You can access the files in the `/tmp/` folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "589859 rows affected.\n",
      "1172645 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "COPY customer_reviews_row from '/tmp/customer_reviews_1998.csv' WITH  CSV;\n",
    "COPY customer_reviews_row from '/tmp/customer_reviews_1999.csv' WITH  CSV;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 2 :  Create a table with columnar storage & load data\n",
    "\n",
    "First, load the extension to use columnar storage in Postgres."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- load extension first time after install\n",
    "CREATE EXTENSION cstore_fdw;\n",
    "\n",
    "-- create server object\n",
    "CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TODO:** Create a `FOREIGN TABLE` called `customer_reviews_col` with the column names contained in the `customer_reviews_1998.csv` and `customer_reviews_1999.csv` files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- create foreign table\n",
    "DROP FOREIGN TABLE IF EXISTS customer_reviews_col;\n",
    "\n",
    "-------------\n",
    "CREATE FOREIGN TABLE customer_reviews_col\n",
    "(\n",
    "    customer_id TEXT,\n",
    "    review_date DATE,\n",
    "    review_rating INTEGER,\n",
    "    review_votes INTEGER,\n",
    "    review_helpful_votes INTEGER,\n",
    "    product_id CHAR(10),\n",
    "    product_title TEXT,\n",
    "    sales_rank BIGINT,\n",
    "    product_group TEXT,\n",
    "    product_category TEXT,\n",
    "    product_subcategory TEXT,\n",
    "    similar_product_ids CHAR(10)[]\n",
    "    \n",
    "    \n",
    ")\n",
    "-------------\n",
    "-- leave code below as is\n",
    "SERVER cstore_server\n",
    "OPTIONS(compression 'pglz');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TODO:** Use the [COPY statement](https://www.postgresql.org/docs/9.2/sql-copy.html) to populate the tables with the data in the `customer_reviews_1998.csv` and `customer_reviews_1999.csv` files. You can access the files in the `/tmp/` folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "589859 rows affected.\n",
      "1172645 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "COPY customer_reviews_col FROM '/tmp/customer_reviews_1998.csv' WITH CSV;\n",
    "COPY customer_reviews_col FROM '/tmp/customer_reviews_1999.csv' WITH CSV;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Compare perfromamce\n",
    "\n",
    "Now run the same query on the two tables and compare the run time. Which form of storage is more performant?\n",
    "\n",
    "**TODO**: Write a query that calculates the average `review_rating` by `product_title` for all reviews in 1995. Sort the data by `review_rating` in descending order. Limit the results to 20.\n",
    "\n",
    "First run the query on `customer_reviews_row`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "5 rows affected.\n",
      "CPU times: user 2.76 ms, sys: 0 ns, total: 2.76 ms\n",
      "Wall time: 4.46 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>review_date</th>\n",
       "        <th>review_rating</th>\n",
       "        <th>review_votes</th>\n",
       "        <th>review_helpful_votes</th>\n",
       "        <th>product_id</th>\n",
       "        <th>product_title</th>\n",
       "        <th>sales_rank</th>\n",
       "        <th>product_group</th>\n",
       "        <th>product_category</th>\n",
       "        <th>product_subcategory</th>\n",
       "        <th>similar_product_ids</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE22YDHSBFYIP</td>\n",
       "        <td>1970-12-30</td>\n",
       "        <td>5</td>\n",
       "        <td>10</td>\n",
       "        <td>0</td>\n",
       "        <td>1551803542</td>\n",
       "        <td>Start and Run a Coffee Bar (Start &amp; Run a)</td>\n",
       "        <td>11611</td>\n",
       "        <td>Book</td>\n",
       "        <td>Business &amp; Investing</td>\n",
       "        <td>General</td>\n",
       "        <td>[&#x27;0471136174&#x27;, &#x27;0910627312&#x27;, &#x27;047112138X&#x27;, &#x27;0786883561&#x27;, &#x27;0201570483&#x27;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE22YDHSBFYIP</td>\n",
       "        <td>1970-12-30</td>\n",
       "        <td>5</td>\n",
       "        <td>9</td>\n",
       "        <td>0</td>\n",
       "        <td>1551802538</td>\n",
       "        <td>Start and Run a Profitable Coffee Bar</td>\n",
       "        <td>689262</td>\n",
       "        <td>Book</td>\n",
       "        <td>Business &amp; Investing</td>\n",
       "        <td>General</td>\n",
       "        <td>[&#x27;0471136174&#x27;, &#x27;0910627312&#x27;, &#x27;047112138X&#x27;, &#x27;0786883561&#x27;, &#x27;0201570483&#x27;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATVPDKIKX0DER</td>\n",
       "        <td>1995-06-19</td>\n",
       "        <td>4</td>\n",
       "        <td>19</td>\n",
       "        <td>18</td>\n",
       "        <td>0898624932</td>\n",
       "        <td>The Power of Maps</td>\n",
       "        <td>407473</td>\n",
       "        <td>Book</td>\n",
       "        <td>Nonfiction</td>\n",
       "        <td>Politics</td>\n",
       "        <td>[&#x27;0226534219&#x27;, &#x27;0226534170&#x27;, &#x27;1931057001&#x27;, &#x27;0801870909&#x27;, &#x27;157230958X&#x27;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AH7OKBE1Z35YA</td>\n",
       "        <td>1995-06-23</td>\n",
       "        <td>5</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>0521469112</td>\n",
       "        <td>Invention and Evolution</td>\n",
       "        <td>755661</td>\n",
       "        <td>Book</td>\n",
       "        <td>Science</td>\n",
       "        <td>General</td>\n",
       "        <td>[&#x27;1591391857&#x27;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATVPDKIKX0DER</td>\n",
       "        <td>1995-07-14</td>\n",
       "        <td>5</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0679722955</td>\n",
       "        <td>The Names (Vintage Contemporaries (Paperback))</td>\n",
       "        <td>264928</td>\n",
       "        <td>Book</td>\n",
       "        <td>Literature &amp; Fiction</td>\n",
       "        <td>General</td>\n",
       "        <td>[&#x27;0140152741&#x27;, &#x27;0679722947&#x27;, &#x27;0140156046&#x27;, &#x27;0679722939&#x27;, &#x27;0679722920&#x27;]</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AE22YDHSBFYIP', datetime.date(1970, 12, 30), 5, 10, 0, '1551803542', 'Start and Run a Coffee Bar (Start & Run a)', 11611, 'Book', 'Business & Investing', 'General', ['0471136174', '0910627312', '047112138X', '0786883561', '0201570483']),\n",
       " ('AE22YDHSBFYIP', datetime.date(1970, 12, 30), 5, 9, 0, '1551802538', 'Start and Run a Profitable Coffee Bar', 689262, 'Book', 'Business & Investing', 'General', ['0471136174', '0910627312', '047112138X', '0786883561', '0201570483']),\n",
       " ('ATVPDKIKX0DER', datetime.date(1995, 6, 19), 4, 19, 18, '0898624932', 'The Power of Maps', 407473, 'Book', 'Nonfiction', 'Politics', ['0226534219', '0226534170', '1931057001', '0801870909', '157230958X']),\n",
       " ('AH7OKBE1Z35YA', datetime.date(1995, 6, 23), 5, 4, 4, '0521469112', 'Invention and Evolution', 755661, 'Book', 'Science', 'General', ['1591391857']),\n",
       " ('ATVPDKIKX0DER', datetime.date(1995, 7, 14), 5, 0, 0, '0679722955', 'The Names (Vintage Contemporaries (Paperback))', 264928, 'Book', 'Literature & Fiction', 'General', ['0140152741', '0679722947', '0140156046', '0679722939', '0679722920'])]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%sql\n",
    "SELECT *\n",
    "FROM customer_reviews_row\n",
    "LIMIT 5\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "20 rows affected.\n",
      "CPU times: user 4.39 ms, sys: 86 µs, total: 4.47 ms\n",
      "Wall time: 891 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>review_rating</th>\n",
       "        <th>product_title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Coaching Volleyball Successfully</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Names (Vintage Contemporaries (Paperback))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Fingerprints of the Gods (Alternative History)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Heavy Light</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Albion&#x27;s Seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Invention and Evolution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Fingerprints of the Gods </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet (Yearling Books)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt (The Wheel of Time, Book 2)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt (Wheel of Time (Audio))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet (Yearling Books)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Wonderful Life</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5, 'Coaching Volleyball Successfully'),\n",
       " (5, 'The Names (Vintage Contemporaries (Paperback))'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'Fingerprints of the Gods (Alternative History)'),\n",
       " (5, 'Heavy Light'),\n",
       " (5, \"Albion's Seed\"),\n",
       " (5, 'Invention and Evolution'),\n",
       " (5, 'Fingerprints of the Gods '),\n",
       " (5, 'A Swiftly Tilting Planet (Yearling Books)'),\n",
       " (5, 'The Great Hunt '),\n",
       " (5, 'The Great Hunt '),\n",
       " (5, 'The Great Hunt (The Wheel of Time, Book 2)'),\n",
       " (5, 'The Great Hunt (Wheel of Time (Audio))'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet (Yearling Books)'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'Wonderful Life')]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%sql\n",
    "\n",
    "SELECT review_rating, product_title\n",
    "FROM customer_reviews_row\n",
    "WHERE CAST(review_date AS TEXT) LIKE '1995-%'\n",
    "ORDER BY review_rating DESC\n",
    "LIMIT 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Then on `customer_reviews_col`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/reviews\n",
      "20 rows affected.\n",
      "CPU times: user 4.46 ms, sys: 0 ns, total: 4.46 ms\n",
      "Wall time: 938 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>review_rating</th>\n",
       "        <th>product_title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Coaching Volleyball Successfully</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Names (Vintage Contemporaries (Paperback))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Fingerprints of the Gods (Alternative History)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Heavy Light</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Albion&#x27;s Seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Invention and Evolution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Fingerprints of the Gods </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet (Yearling Books)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt (The Wheel of Time, Book 2)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>The Great Hunt (Wheel of Time (Audio))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet (Yearling Books)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>A Swiftly Tilting Planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Wonderful Life</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5, 'Coaching Volleyball Successfully'),\n",
       " (5, 'The Names (Vintage Contemporaries (Paperback))'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'Fingerprints of the Gods (Alternative History)'),\n",
       " (5, 'Heavy Light'),\n",
       " (5, \"Albion's Seed\"),\n",
       " (5, 'Invention and Evolution'),\n",
       " (5, 'Fingerprints of the Gods '),\n",
       " (5, 'A Swiftly Tilting Planet (Yearling Books)'),\n",
       " (5, 'The Great Hunt '),\n",
       " (5, 'The Great Hunt '),\n",
       " (5, 'The Great Hunt (The Wheel of Time, Book 2)'),\n",
       " (5, 'The Great Hunt (Wheel of Time (Audio))'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet (Yearling Books)'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'A Swiftly Tilting Planet'),\n",
       " (5, 'Wonderful Life')]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%sql\n",
    "SELECT review_rating, product_title\n",
    "FROM customer_reviews_col\n",
    "WHERE CAST(review_date AS TEXT) LIKE '1995-%'\n",
    "ORDER BY review_rating DESC\n",
    "LIMIT 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion: We can see that the columnar storage is faster!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
